package cz.acies.uniql.connect;

import java.io.Serializable;
import java.sql.*;
import java.util.*;

import cz.acies.uniql.dialect.*;

public class ConnectorHelper implements Serializable
{
	private static final long serialVersionUID = 5478780120189118067L;

	public ConnectorHelper() {}

	//----------------------- MetaData methods ---------------------------------

	public static Map<String, String> getTableIndexRegister(Connection conn, String table) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getIndexInfo(null, null, table, false, true);
		Map<String, String> register = new HashMap<String, String>();
		while (rs.next()) {
			if (rs.getObject("COLUMN_NAME") != null) {
				register.put(rs.getString("COLUMN_NAME"), rs.getString("COLUMN_NAME"));
			}
		}
		rs.close();
		return register;
	}
	
	public static ResultSet getTableIndex(Connection conn, String table) throws SQLException
	{
		DatabaseMetaData dbmd = conn.getMetaData();
		String catalog = null;									
		String schema = null;									
		return dbmd.getIndexInfo(catalog, schema, table, false, true);
	}
		
	public static Map<String, String> getTablePKRegister(Connection conn, String table) throws SQLException
	{
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getPrimaryKeys(null, null, table);
		Map<String, String> register = new HashMap<String, String>();
		while(rs.next()) {
			if (rs.getString("TABLE_NAME").equals(table)) {
				register.put(rs.getString("COLUMN_NAME"),rs.getString("COLUMN_NAME"));
			}
		}
		rs.close();
		return register;
	}

	public static ResultSet getPrimaryKeys(Connection conn, String table) throws SQLException
	{
		DatabaseMetaData dbmd = conn.getMetaData();
		String catalog = null;									
		String schema = null;									
		return dbmd.getPrimaryKeys(catalog, schema, table);
	}

	public static Map<String, String> getColumnNameRegister(Connection conn, String table) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getColumns(null, null, table, null);
		Map<String, String> register = new HashMap<String, String>();
		while(rs.next()) {
			if (rs.getString("TABLE_NAME").equals(table)) {
				register.put(rs.getString("COLUMN_NAME"),rs.getString("COLUMN_NAME"));
			}
		}
		rs.close();
		return register;		
	}

	public static List<String> getColumnNameList(Connection conn, String table) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getColumns(null, null, table, null);
		List<String> list = new ArrayList<String>();
		while(rs.next()) {
			if (rs.getString("TABLE_NAME").equalsIgnoreCase(table)) {
				list.add(rs.getString("COLUMN_NAME"));
			}
		}
		rs.close();
		return list;		
	}

	public static ResultSet getColumns(Connection conn, String table) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		String catalog = null;							
		String schema = null;							
		String column = null;
		return dbmd.getColumns(catalog, schema, table, column);
	}

	public static List<String> getTableList(Connection conn) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();		
		String[] types = { "TABLE" };
		
		ResultSet rs = dbmd.getTables(null, null, null, types);
		List<String> list = new ArrayList<String>();
		while(rs.next()) {
			if (rs.getString("TABLE_TYPE").equals("TABLE")) {
				list.add(rs.getString("TABLE_NAME"));
			}
		}
		rs.close();
		return list;
	}
		
	public static ResultSet getTables(Connection conn) throws SQLException {
		return getTables(conn, null);
	}

	public static ResultSet getTables(Connection conn, String schema) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		String catalog = null;									//conn.getCatalog();
		String table = null;
		String[] types = { "TABLE" };							//ConnectorHelper.getTableTypesArray(conn);
		return dbmd.getTables(catalog, schema, table, types);
	}
	
	/**
	 * Vytvo�� ResultSet v�ech dostupn�ch datab�z�
	 * 
	 * @param conn - objekt typu Connection 
	 * 
	 * @return ResultSet - v�ech DB
	 * @author "Petr Zelenka"
	 */
	public static ResultSet getDatabases(Connection conn) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		return dbmd.getCatalogs();
	}

	public static String getSQLKeywords(Connection conn) throws SQLException {
		String keywords = conn.getMetaData().getSQLKeywords();
		return keywords;
	}

	public static List<String> getSQLKeywordList(Connection conn) throws SQLException {
		List<String> list = new ArrayList<String>();
		String keywords = conn.getMetaData().getSQLKeywords();
		StringTokenizer st = new StringTokenizer(keywords,",");
		while (st.hasMoreTokens()) {
			String word = st.nextToken();
			list.add(word);
		}
		return list;
	}

	public static List<String> getTableTypesList(Connection conn) throws SQLException
	{
		List<String> list = new ArrayList<String>();
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getTableTypes();
		ResultSetMetaData rsmd = rs.getMetaData();
		while(rs.next()) {
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				list.add(rs.getString(i+1));
			}
		}
		rs.close();
		return list;
	}

	public static String[] getTableTypesArray(Connection conn) throws SQLException
	{
		DatabaseMetaData dbmd = conn.getMetaData();
		ResultSet rs = dbmd.getTableTypes();
		ResultSetMetaData rsmd = rs.getMetaData();
		rs.last();
		String[] types = new String[rs.getRow()];
		while(rs.next()) {
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				types[i] = rs.getString(i+1);
			}
		}
		rs.close();
		return types;
	}

	//-----------------------  -------------------------------------------------

	public static final String printTablesMessage(ResultSet rs)throws SQLException {
		int i = 0;
		while (rs.next()) {
			i++;
			System.out.println();
			System.out.print("Table catalog: " + rs.getObject("TABLE_CAT"));
			System.out.print(" schema: " + rs.getObject("TABLE_SCHEM"));
			System.out.print(" name: " + rs.getObject("TABLE_NAME"));
			System.out.print(" type: " + rs.getObject("TABLE_TYPE"));
			System.out.print(" explanatory comment: " + rs.getObject("REMARKS"));
		}
		System.out.println();
		System.out.println("The number of above tables is "+i+".\r\n");
		if(i==0) return null;
		else {
			return (rs.getString(
				"TABLE_SCHEM")==null?"":"\""+rs.getString("TABLE_SCHEM")+"\"\\")
				+"\""+rs.getObject("TABLE_NAME")+"\""
				+(rs.getString("TABLE_CAT")==null?"":"\"@"+rs.getString("TABLE_CAT")+"\""
			);
		}				
	}

	public static final String printColumnsMessage(ResultSet rs) throws SQLException {
		int i=0;
		while (rs.next()) {
			i++;
			for(int j = 1; j <= 18; j++) {
				System.out.print(rs.getObject(j)+"  ");
			}
			System.out.println();
		}
		System.out.println("The number of above columns is "+i+".\r\n");
		if (i == 0) return null;
		else return rs.getString("COLUMN_NAME");
	}

	public static void printResultSet(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		while(rs.next()) {
			for (int i = 0; i < rsmd.getColumnCount(); i++) {
				System.out.println(rsmd.getColumnName(i+1)+" = "+rs.getString(i+1));
			}
			System.out.println();
		}
	}
	/*
	public static void printList(List<String> list) throws SQLException {
		for (Enumeration e = list.elements() ; e.hasMoreElements() ;) {
			System.out.println(e.nextElement());
		}		
	}
	*/
	//-----------------------  -------------------------------------------------
	
	public static Connection getConnection(int type) throws SQLException {
		try {
			Properties prop = new Properties();
			Dialect dialect = null; 
			switch (type) {
				case SQLConnect.FIREBIRD: {
					prop.put("driver","org.firebirdsql.jdbc.FBDriver");
					prop.put("prefix","jdbc:firebirdsql:");
					prop.put("host","localhost");
					prop.put("database","smart4web");
					break;
				}
				case SQLConnect.MSSQL: {
					prop.put("driver","com.jnetdirect.jsql.JSQLDriver");
					prop.put("prefix","jdbc:JSQLConnect:");
					prop.put("host","192.168.168.7");
					prop.put("database","Smart4web");
					prop.put("user","sa");
					prop.put("password","tucnak");
					prop.put("SelectMethod","Direct");				
					
					dialect = new MSSQLDialect();
					break;
				}
				case SQLConnect.ORACLE: {
					/*
					prop.put("driver","com.sap.dbtech.jdbc.DriverSapDB");
					prop.put("prefix","jdbc:sapdb:");
					prop.put("host","localhost");
					prop.put("database","smarterp");
					prop.put("user","dba");
					prop.put("password","dba");
					prop.put("autocommit","ON");
					//prop.put("isolation","TRANSACTION_READ_COMMITTED");
					prop.put("isolation","TRANSACTION_SERIALIZABLE");
					prop.put("unicode","ASCII");
		
					//DBM session = DBM.dbrootDBM ("DBM", <dependent_path>);
					dialect = new MaxDBDialect();				
					*/				
					break;
				}
				case SQLConnect.DBASE: {
					/*
					prop.put("driver","zyh.sql.dbf.DBFDriver");
					prop.put("prefix","jdbc:DBF:");
					prop.put("host",IOUtils.getExternalDir()+"/data");
					prop.put("user","");
					prop.put("password","");				
					
					dialect = new MySQLDialect();
					break;
					*/
				}	
				/*
				case HSQLDB: {
					prop.put("driver","org.hsqldb.jdbcDriver");
					prop.put("prefix","jdbc:hsqldb:");
					prop.put("host","../data");
					prop.put("database","smart4web");
					//prop.put("port","3306");
					prop.put("user","sa");
					prop.put("password","");				
					
					//dialect = new MySQLDialect();
					break;
				}
				*/
			}
			SQLConnect db = new SQLConnect(prop, dialect);
			return db.getConnection();
		} catch(SQLException ex) {
			throw ex;
		} catch(Exception ex) {
			ex.printStackTrace();
		}		
		return null;
	}
	
	//----------------------- Functions methods --------------------------------
	
	public static List<String> getTimeDateFunctionList(Connection conn) throws SQLException {
		List<String> list = new ArrayList<String>();
		DatabaseMetaData dbmd = conn.getMetaData();
		String send = dbmd.getTimeDateFunctions(); 
		StringTokenizer st = new StringTokenizer(send,",");
		while (st.hasMoreTokens()) {
			String word = st.nextToken();
			list.add(word);
		}		
		return list;
	}

	public static String getTimeDateFunctions(Connection conn) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		String str = dbmd.getTimeDateFunctions(); 
		return str;
	}
	
	/**/
	
	public static List<String> getStringFunctionList(Connection conn) throws SQLException {
		List<String> list = new ArrayList<String>();
		DatabaseMetaData dbmd = conn.getMetaData();
		String send = dbmd.getStringFunctions();//getTimeDateFunctions(); 
		StringTokenizer st = new StringTokenizer(send,",");
		while (st.hasMoreTokens()) {
			String word = st.nextToken();
			list.add(word);
		}		
		return list;
	}

	public static String getStringFunctions(Connection conn) throws SQLException {
		DatabaseMetaData dbmd = conn.getMetaData();
		String str = dbmd.getStringFunctions(); 
		return str;
	}

	//-----------------------  -------------------------------------------------
	
	public static String getSqlTypeName(int type) {
		switch (type) {
	    	case Types.TIMESTAMP:		return "TIMESTAMP";
	    	case Types.TIME:			return "TIME";
	    	case Types.DATE:			return "DATE";
	    	
	    	case Types.CHAR: 			return "CHAR";
	    	case Types.VARCHAR: 		return "VARCHAR";
			case Types.LONGVARCHAR: 	return "LONGVARCHAR";
			case Types.VARBINARY:		return "VARBINARY";
	    	case Types.LONGVARBINARY:	return "LONGVARBINARY";			
			case Types.BLOB:			return "BLOB";
			case Types.CLOB:			return "CLOB";
			case Types.BINARY:			return "BINARY";			
			
	    	case Types.BOOLEAN:			return "BOOLEAN";
	    	
			case Types.BIT: 			return "BIT";			
	    	case Types.NUMERIC:			return "NUMERIC";
	    	case Types.DECIMAL:			return "DECIMAL";
	    	case Types.DOUBLE:			return "DOUBLE";
	    	case Types.FLOAT:			return "FLOAT";
	    	case Types.REAL:			return "REAL";	    	
			case Types.TINYINT:			return "TINYINT";
	    	case Types.INTEGER:			return "INTEGER";
	    	case Types.BIGINT:			return "BIGINT";
 	    	
	    	case Types.ARRAY:			return "ARRAY";	    	
	    	case Types.REF:				return "REF";
	    	case Types.STRUCT:			return "STRUCT";
	    	
	    	case Types.DATALINK:		return "DATALINK";
	    	case Types.DISTINCT:		return "DISTINCT";
	    	case Types.JAVA_OBJECT:		return "JAVA_OBJECT";
	    	case Types.OTHER:			return "OTHER";
	    	case Types.NULL:			return "NULL";	    	

    	}
		return null;
	}
	/*
	public static final int MYSQL = 0;
	public static final int MSSQL = 1;
	public static final int MAXDB = 2;
	public static final int FIREBIRD = 3;
	public static final int HSQLDB = 4;	
	public static final int MCKOI = 5;
	*/
}
